import json
import copy
import xlsxwriter
from io import BytesIO
from apis import api_routes
from flask import g, request, jsonify, abort, Response
from security.decoration import certification
from typing import List
from typing import Union
from sqlalchemy import and_
from sqlalchemy import text
from urllib.parse import unquote, quote
from models.order.tasks import Tasks
from models.course.course import Course
from models.course.detailed import Detailed
from models.facility.facility import Facility
from models.course.serial import Serial
from public.statistics import calculate_cpk


@api_routes.route("/details", methods=["GET"])
@certification(auth=True, role=None)
def func_details_get_api():
    """
    根据产品SN码查询生产详细信息，完成API开发接口
    注意: title长度不是固定的， 有多少个title 就有多少 values
    http://127.0.0.1:8080/api/details
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "sequence"     string     ****   产品SN码
    }
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 产品SN码查询生产详细信息
    """
    sequence = request.args.get("sequence", default=None, type=str)

    if sequence is None or len(sequence) == 0:
        abort(400)

    db_course: Union[Course, List] = g.session.query(Course).filter(and_(
                                        Course.sequence == sequence, Course.mark > 0
                                    )).first()
    if db_course is None:
        return jsonify({'status': 200, 'msg': "根据产品SN码没有查询到产品信息"})

    data: List[Detailed] = g.session.query(Detailed).filter(Detailed.sequence == sequence).order_by(Detailed.id).all()
    resp: list = []
    for index, itr in enumerate(data):
        db_message_resp: list = []
        db_tk: Tasks = g.session.query(Tasks).filter(Tasks.id == itr.tasks_id).first()

        db_ft: Facility = g.session.query(Facility).filter(Facility.id == itr.facility_id).first()
        if itr.message is not None:
            date_dict_all: dict = json.loads(itr.message)
            for key, value in date_dict_all.items():
                db_message_resp.append({
                    "key": key,
                    "value": value
                })

        resp.append({
            "sequence": sequence,
            "time": itr.time.strftime("%Y-%m-%d %H:%M:%S"),
            "serials": db_ft.serials,
            "tf_name": db_ft.name,
            "db_message_resp": db_message_resp,
            "status": "合格" if itr.status else "不合格",
            "error": itr.error,
            "numbers": itr.numbers,
            "tk_name": db_tk.name
        })

    docs: dict = {
        "sequence": "产品SN码",
        "time": "数据采集时间",
        "tk_name": "工单名称",
        "orders": "订单编号",
        "tf_name": "设备名称",
        "serials": "设备编号",
        "message": "采集数据信息， 格式：array[json], json内容属性不固定",
        "status": "是否合格",
        "numbers": "第几次加工",
        "error": "不合格原因"
    }
    return jsonify({'status': 200, 'data': resp, "docs": docs})


@api_routes.route("/details/output", methods=["GET"])
@certification(auth=True, role=None)
def func_details_output_get_api():
    """
    根据产品SN码生产信息导出EXCEL文件，完成API开发
    http://127.0.0.1:8080/api/details/output
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "sequence"     string     ****   产品SN码
    "name"         string            导出文件名称
    }
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 产品SN码查询生产详细信息
    """
    sequence = request.args.get("sequence", default=None, type=str)
    name: Union[str, None] = request.args.get("name", default=None, type=str)
    if sequence is None or len(sequence) == 0:
        abort(400)

    file_name: str = sequence
    db_course: Union[Course, List] = g.session.query(Course).filter(Course.sequence == sequence).first()
    if db_course is None:
        return Response(), 200

    if name is not None and len(name) != 0:
        file_name = sequence
    file_name = "{}.xlsx".format(file_name)

    task: dict = {"tk_id": 0, "tk_name": None, "orders": None}
    facility: dict = {"ft_id": 0, "tf_name": None, "serials": None}

    title: list = [
        "产品SN码",
        "数据采集时间",
        "工单名称",
        "订单编号",
        "设备名称",
        "设备编号",
        "采集数据信息",
        "是否合格",
        "第几次加工",
        "不合格原因",
        "物料信息"
    ]
    output = BytesIO()
    workbook = xlsxwriter.Workbook(output, {'in_memory': True})
    details = workbook.add_worksheet("产品SN码生产信息表")
    details.write_row(row=0, col=0, data=title)

    data: List[Detailed] = g.session.query(Detailed).filter(Detailed.sequence == sequence).order_by(Detailed.id).all()
    for index, itr in enumerate(data):
        first: bool = True
        title, array, message, cpk = [], [], [], []
        db_task: Tasks = g.session.query(Tasks).filter(
                            Tasks.id == itr.tasks_id
                        ).first()
        db_ft: Facility = g.session.query(Facility).filter(
                                Facility.id == itr.facility_id
                            ).first()
        if itr.message:
            date_dict_all = json.loads(itr.message)

        serials: list = []
        db_serial: List[Serial] = g.session.query(Serial).filter(Serial.sequence == sequence).order_by(Serial.id).all()
        for itr1 in db_serial:
            serials.append({
                "serial": itr1.serial,
                "codes": itr1.codes
            })

    workbook.close()
    output.seek(0)
    resp = Response(output, content_type='application/octet-stream')
    resp.headers["Content-Disposition"] = "attachment; filename={0}; filename*=utf-8''{0}".format(quote(file_name))
    resp.headers.set("Access-Control-Expose-Headers", "*")
    return resp, 200
